# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://docs.scrapy.org/en/latest/topics/item-pipeline.html


# useful for handling different item types with a single interface
from itemadapter import ItemAdapter
import pymysql

class PyMysqlPipeline:
    def __init__(self):
        # 数据库信息
        # 存在中文的时候，连接需要添加charset = 'utf8'，否则中文显示乱码
        dbparams = {
            'host':'127.0.0.1',
            'port':3306,
            'user':'root',
            'password':'root',
            'database':'jianshu',
            'charset':'utf8'
        }
        # 连接数据库
        self.conn = pymysql.connect(**dbparams)
        # 创建游标
        self.cursor = self.conn.cursor()
        self._sql = None

    def process_item(self, item, spider):
        # 执行SQL
        self.cursor.execute(self.sql,(item['title'],item['content'], item['url']))
        # 提交，不然无法保存新建或者修改的数据
        self.conn.commit()
        return item

    @property
    def sql(self):
        if not self._sql:
            self._sql = """
            insert into article(id, title, content, url) values(null, %s,%s,%s)
            """
        return self._sql

